﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace BusinessLogicLayer
{

   public class ItemFacade
    {
        InventorySoreDataContext context;
        Item item;
        public ItemFacade()
        {
            context = new InventorySoreDataContext();
        }



        public float GetPrice(string itemCode,string supplierid)
        {
            
                float itemPrice = (from price in context.TenderLists
                                   where price.ItemCode == itemCode && price.SupplierID == supplierid
                                   select price.Price).SingleOrDefault();
               
            
         
            return itemPrice;
          
        }


        public IQueryable FindAllCategory()
        {
            try
            {
                var categories = from cate in context.Categories select cate;
                return categories;
            }
            catch (Exception ee)
            {
                return null;
            }
         
        }
        //public double getItemPrice(string itemcode)
        //{
        //    try
        //    {
        //        var price=from item in context.Items where item.ItemCode== itemcode select item.Supplier.p          }
        //    catch (Exception e)
        //    {
               
        //    }
        //}
        public IQueryable FindAllByCategory(string categoryID)
        {
            try
            {
                var items = from it in context.Items where it.CategoryID == categoryID select it;
                return items;
            }

            catch (Exception ee)
            {
                return null;
            }
        }

        public Item FindByItemID(string itemID)
        {
            try
            {
                var item = (from it in context.Items where it.ItemCode == itemID select it).SingleOrDefault();
                return item;
            }
            catch (Exception ee)
            {
                return null;
            }
        }
        public string FindSupplierID(string itemCode)
        {
            var supplierID = (from item in context.Items
                         where item.ItemCode == itemCode
                         select item.Supplier1).SingleOrDefault();
            return supplierID;
        }

        public int CalculateBalance(string itemcode,int adjustmentQty,string operation)
        {
          
            var item =FindByItemID(itemcode);

            if (operation.Equals("+"))
            {
                item.Stock += adjustmentQty;
                item.LogicStock += adjustmentQty;
            }
            else if (operation.Equals("-"))
            {
                item.Stock -= adjustmentQty;
                item.LogicStock -= adjustmentQty;
            }
          UpdateLogicStock(item.ItemCode,item.LogicStock);
           UpdateStock(item.ItemCode,item.Stock);
            context.SubmitChanges();
            return item.Stock;
        }
        public string GetStockCardID()
        {
       

            string prefix = "SCD100";
            int count = (from stock in context.StockCards select stock).Count();
            count++;
           string id = prefix + count.ToString();
           return id;
        }
        public IQueryable FindStockCardByItemCode(string itemCode)
        {
            var stock = (from stc in context.StockCards where stc.ItemCode == itemCode 
                 select new 
            {
                Dates= stc.Date, 
              Dept_Supplier=stc.Description,
                Quantity = stc.Operation+stc.Quantity,
              Balance=stc.Balance,
              //Operation=stc.Operation,
            });
            return stock;
        }

        public String GetItemCodeByDesc(String desc)

        {
            var itemcode = (from item in context.Items where item.Description==desc select item).SingleOrDefault();
            return itemcode.ItemCode;
        }
       

        public IQueryable FindItemDetail_StockCard(String itemCode)
        {
            var item = (from it in context.StockCards
                        join itd in context.Items on it.ItemCode equals itd.ItemCode
                        where it.ItemCode == itemCode
                        select new
                        {
                            it.ItemCode,
                            itd.Description,
                            itd.Bin,
                            itd.UOM,
                            itd.Supplier1,
                            itd.Supplier2,
                            itd.Supplier3
                        });
            return item;
        }


        //public IQueryable FindReorderReportBySelectedMonth(int sMonth, int year)
        //{
        //    var items = (from it in context.Items
        //                 join od in context.OrderDetails on it.ItemCode equals od.ItemCode
        //                 join o in context.Orders on od.PONo equals o.PONo
        //                 where o.ExpectedDate.Month == sMonth && o.ExpectedDate.Year == year
        //                 select new
        //                 {
        //                     ItemCode = it.ItemCode,
        //                     Description = it.Description,
        //                     Stock = it.Stock,
        //                     ReorderLevel = it.ReorderLevel,
        //                     ReorderQty = it.ReorderQty,
        //                     PONo = od.PONo,
        //                     ExpectedDate = o.ExpectedDate
        //                 });
        //    return items;
        //}

        public IQueryable FindReorderReportBySelectedMonth(int sMonth, int year)
        {
            var items = (from it in context.Items
                         join od in context.OrderDetails on it.ItemCode equals od.ItemCode
                         join o in context.Orders on od.PONo equals o.PONo
                         //where o.ExpectedDate.Month == sMonth && o.ExpectedDate.Year == year
                         select new
                         {
                             ItemCode = it.ItemCode,
                             Description = it.Description,
                             Stock = it.Stock,
                             ReorderLevel = it.ReorderLevel,
                             ReorderQty = it.ReorderQty,
                             PONo = od.PONo,
                             ExpectedDate = o.ExpectedDate
                         });
            return items;
        }


        public IQueryable FindSuppliersByItemCode(string itemCode)
        {
            var suppliers = (from item in context.Items
                             join supplier in context.Suppliers on item.Supplier1 equals supplier.SupplierID
                             select new
                                 {
                                     supplier.SupplierID,
                                     supplier.PhoneNo
                                 });
            return suppliers;
        }
        public IQueryable FindFirstPriceByItemCode(string itemCode,string supplierID)
        {
            var price = from tenderlist in context.TenderLists where tenderlist.ItemCode == itemCode && tenderlist.SupplierID == supplierID select tenderlist.Price;
            return price;
        }
        public void UpdateStock(string itemID, int stock)
        {
            item = FindByItemID(itemID);
            item.Stock = stock;
            context.SubmitChanges();
        }

        public void UpdateLogicStock(string itemID, int logicStock)
        {
            item = FindByItemID(itemID);
            item.LogicStock = logicStock;
            context.SubmitChanges(System.Data.Linq.ConflictMode.ContinueOnConflict);
        }
        public void insertStockCard(StockCard sc)
        {
            try
            {
                context.StockCards.InsertOnSubmit(sc);
                context.SubmitChanges();
            }
            catch(Exception ee)
            {
                
            }
        }
        public void UpdateItem(string itemID, int reorderLevel, int reorderQty,string bin)
        {
            item = FindByItemID(itemID);
            item.ReorderLevel = reorderLevel;
            item.ReorderQty = reorderQty;
            item.Bin = bin;
            context.SubmitChanges();
        }

        public Item FindItemByID(string itemCode)
        {
            try
            {
                var query = (from item in context.Items where item.ItemCode == itemCode select item).Single();
                return query;
            }
            catch (Exception ee)
            {
                return null;
            }
        }
        public List<string> GetSuppliers(string itemCode)
        {
            var query = (from item in context.Items
                         where item.ItemCode == itemCode
                         select item).SingleOrDefault();
            List<string> suppliers = new List<string>();
            Item itm=query;
            suppliers.Add(itm.Supplier1);
            suppliers.Add(itm.Supplier2);
            suppliers.Add(itm.Supplier3);
            return suppliers;
        }

    }
}
